package com.boot.study;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.boot.study.domain.DemoData;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: lkz
 * @Title: DataDemoTest
 * @Description: TODO
 * @Date: 2023/8/9 15:53
 */

public class DataDemoTest {


    public static void main(String[] args) throws IOException {
        ClassPathResource classPathResource = new ClassPathResource("excel/demoData.xlsx");
        InputStream templateFileName = classPathResource.getInputStream();
        String fileName = "D://" + "demoData" + System.currentTimeMillis() + "demo.xlsx";

        // 2. 真实的模拟数据
        List<DemoData> list = dataList();

        // 3. 数据写出
        ExcelWriter writer = EasyExcel.write(fileName).withTemplate(templateFileName).build();
        // 3.1 操作第一个sheet(记得注册自定义的CellWriteHandler)
        WriteSheet sheet = EasyExcel.writerSheet(0).registerWriteHandler(new CustomCellWriteHandler()).build();
        // 3.2 填充列表数据
        writer.fill(list, FillConfig.builder().forceNewRow(Boolean.TRUE).build(), sheet);
        // 3.3 填充其它动态信息
        Map<String, Object> extra = new LinkedHashMap<>();
        // 单位：10%
        extra.put("taxRate", 10);

        //3.4 设置强制计算公式：不然公式会以字符串的形式显示在excel中
        Workbook workbook = writer.writeContext().writeWorkbookHolder().getWorkbook();
        workbook.setForceFormulaRecalculation(true);

        // 3.5 数据刷新
        writer.fill(extra, sheet).finish();

        System.out.println("over");
    }




    private static List<DemoData> dataList()
    {
        ArrayList<DemoData> list = new ArrayList<>();
        for (int i = 1; i <= 20; i++)
        {
            DemoData demoData = new DemoData();
            demoData.setOrderId("202001180000" + i);
            demoData.setGoodName("商品名称" + i);
            demoData.setGoodPrice(new BigDecimal(i));
            demoData.setNum(i);

            list.add(demoData);
        }
        return list;
    }


}
 class CustomCellWriteHandler implements CellWriteHandler
{
    private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class);


    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {


        // 这里可以对cell进行任何操作
        System.out.println("进入第" +  cell.getRowIndex() + "行,第" + cell.getColumnIndex() + "列数据...");
        if (cell.getRowIndex() >= 4 && 4 == cell.getColumnIndex())
        {
            // 税价 = 含税单价 * 数量 * 税率
            // 以第4行数据为例：税价 = C5*D5*$C$1
            int actualCellRowNum = cell.getRowIndex() + 1;
            cell.setCellFormula("C" + actualCellRowNum +"*D" + actualCellRowNum + "*$C$1");
            System.out.println("第" +  cell.getRowIndex() + "行,第" + cell.getColumnIndex() + "税价写入公式完成--:"+cell.getCellFormula());

        }

        if (cell.getRowIndex() >= 4 && 5 == cell.getColumnIndex())
        {
            // 总价 = 含税单价 * 数量
            // 以第4行数据为例：税价 = C5*D5
            int actualCellRowNum = cell.getRowIndex() + 1;
            cell.setCellFormula("C" + actualCellRowNum + "*D" + actualCellRowNum);
            System.out.println("第" +  cell.getRowIndex() + "行,第" + cell.getColumnIndex() + "总价写入公式完成");
        }



    }
}


